USE [HomestayWebDB]
GO

/****** Object:  View [dbo].[ListAllAddressProDis_View]    Script Date: 11/26/2012 17:40:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO









ALTER VIEW [dbo].[ListAllAddressProDis_View]
AS
SELECT ISNULL(ROW_NUMBER() OVER(ORDER BY TMP.ParentName ASC),0) AS ID ,* 
FROM (
SELECT 
		Regions.ParentName +', '+Regions.Name AS Address
		,Regions.ParentName
		,Regions.Name
FROM Regions
WHERE Regions.Level =2

UNION ALL
SELECT 
		Regions.Name +', '+Regions.ParentName AS Address
		,Regions.ParentName
		,Regions.Name
FROM Regions
WHERE Regions.Level =2 

UNION ALL
SELECT  
		Regions.Name  AS Address
		,Regions.ParentName
		,Regions.Name
FROM Regions
) AS TMP








GO


